package com.hc.serviceImpl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import com.hc.bean.BSizeBean;
import com.hc.bean.GradeBean;
import com.hc.comm.CommData;
import com.hc.comm.Service.BSizeServie;
import com.hc.dao.RunSqlDao;
import com.hc.daoImpl.RunSqlDaoImpl;
import com.hc.service.CompanyGradeService;
import com.hc.service.VlanGradeService;

public class VlanGradeServiceImpl implements VlanGradeService {


	public static final String VLANGRADECOMPANYTEXTSELECTVALUE = "公司查询";

	@Override
	public List<GradeBean> getVlanGrade(String startDate, String stopDate,String vlanGradeCompanyTextSelect,String vlanGradeCompanyText) {

		
		
		
		Map<String, Integer> webTestCountList = getWebTestCountList(startDate,stopDate, vlanGradeCompanyTextSelect, vlanGradeCompanyText);;

		Map<String, Integer> webtimedelayGradeList = getWebtimedelayGradeList(startDate,stopDate, vlanGradeCompanyTextSelect, vlanGradeCompanyText);
		Map<String, Integer> webShakeGradeList = getWebShakeGradeListList(startDate,stopDate, vlanGradeCompanyTextSelect, vlanGradeCompanyText);	
		Map<String, Integer> weblostRateGradeList = getWeblostRateGradeListList(startDate,stopDate, vlanGradeCompanyTextSelect, vlanGradeCompanyText);
		
		
		Map<String, Integer> downFileTestCountList = getDownFileTestCountList(startDate,stopDate, vlanGradeCompanyTextSelect, vlanGradeCompanyText);;

		
		Set<String> vlanList= 	getVlanIdSet(webTestCountList,downFileTestCountList );
		
		

		Map<String, Integer> downFileGradeCountList = getDownFileGradeCountList(startDate,stopDate, vlanGradeCompanyTextSelect, vlanGradeCompanyText);
		
		
		List<GradeBean> list  = new ArrayList<GradeBean>();
		
		for(String key:vlanList){
			
			GradeBean bean = new GradeBean();
			
			
			bean.setId(0);
			bean.setDownFileGrade(0);
			
			
			if(downFileTestCountList.get(key)!=null){
				bean.setDownFileTestCount(downFileTestCountList.get(key));
				
			}
			
			bean.setTime(startDate+" 至  "+stopDate);
			
			if(webtimedelayGradeList.get(key)!=null&&webTestCountList.get(key)!=null){
				bean.setWebtimedelayGrade(webtimedelayGradeList.get(key)/webTestCountList.get(key));

				
			}
			if(webShakeGradeList.get(key)!=null&&webTestCountList.get(key)!=null){
				bean.setWebShakeGrade(webShakeGradeList.get(key)/webTestCountList.get(key));

				
			}
			if(weblostRateGradeList.get(key)!=null&&webTestCountList.get(key)!=null){
				bean.setWeblostRateGrade(weblostRateGradeList.get(key)/webTestCountList.get(key));
			}
			
			if(downFileGradeCountList.get(key)!=null&&downFileTestCountList.get(key)!=null){
				
				bean.setDownFileGrade(downFileGradeCountList.get(key)/downFileTestCountList.get(key));
				
			}
			
			if(webTestCountList.get(key)!=null){
				bean.setWebTestCount(webTestCountList.get(key));
				
			}
			bean.setVlanId(key);
			
			list.add(bean);
			
		}
		
		
		
		
		
		return list;
	}

	
private Map<String, Integer> getDownFileGradeCountList(String startDate,
			String stopDate,String vlanGradeCompanyTextSelect,String vlanGradeCompanyText) {

	
	 Map<String, Integer> map = new HashMap<String, Integer>();
	
	String downFileBSizeSql = getDownFileBSizeSql();

	RunSqlDao dao = new RunSqlDaoImpl();
	List list = dao.runSqlQueryNoKey(downFileBSizeSql);




		for (Object obj : list) {

			List bsize = (List) obj;

			
			if(bsize.get(0).toString()==null){
				continue;
				
			}
			
			BSizeBean bean = BSizeServie.getBSizeBean(bsize.get(0).toString());

			if(bean==null){
				continue;
				
			}
			
			String sql1 = "select SUM(100) as sumCount , vlanId1 , vlanId2 from downFileTestData where speedMax >="
					+ bean.getMaxSpeed()
					+ " and bSizeNumber = '"
					+ bsize.get(0).toString()
					+ "'  and cmdTime BETWEEN '"
					+ startDate + "' and '" + stopDate + "'";
			
			
			if(VLANGRADECOMPANYTEXTSELECTVALUE.equals(vlanGradeCompanyTextSelect)){
				
				sql1+=" and vlanId1 like '%"+vlanGradeCompanyText+"%'";
			}
			
			sql1+=" group by  vlanId1 , vlanId2";
			
			
			List result = dao.runSqlQuery(sql1);

			if(result!=null&&result.size()>0){
				
				for(Object obj1:result){
					
					Map map1 =(Map) obj1;
					String key = map1.get("vlanId1").toString()+"_"+map1.get("vlanId2").toString();
					
					if(map.get(key)!=null){
						
						map.put(key, Integer.parseInt(map1.get("sumCount").toString())+map.get(key));
					}else{
						map.put(key, Integer.parseInt(map1.get("sumCount").toString()));
						
					}
				
				}
			}
			

			String sql2 = "select CAST(SUM( 100*(1 - ( speedMax-"
					+ bean.getMinSpeed()
					+ ")/"
					+ (bean.getMaxSpeed() - bean.getMinSpeed())
					+ " ) )  as INT) as sumCount , vlanId1 , vlanId2  from downFileTestData where speedMax <="
					+ bean.getMaxSpeed() + " and speedMax>"+bean.getMinSpeed()+" and bSizeNumber = '"
					+ bsize.get(0).toString() + "'  and cmdTime BETWEEN '"
					+ startDate + "' and '" + stopDate + "'";
			
		if(VLANGRADECOMPANYTEXTSELECTVALUE.equals(vlanGradeCompanyTextSelect)){
				
				sql2+=" and vlanId1 like '%"+vlanGradeCompanyText+"%'";
			}
			
			sql2+=" group by  vlanId1 , vlanId2";
			
			
			List result2 = dao.runSqlQuery(sql2);

			if(result2!=null&&result2.size()>0){
				
				for(Object obj1:result2){
					
					Map map1 =(Map) obj1;
					String key = map1.get("vlanId1").toString()+"_"+map1.get("vlanId2").toString();
					
					if(map.get(key)!=null){
						
						map.put(key, Integer.parseInt(map1.get("sumCount").toString())+map.get(key));
					}else{
						map.put(key, Integer.parseInt(map1.get("sumCount").toString()));
						
					}
				
				}
			}

	}
	return map;

}





private String getDownFileBSizeSql() {
	String sql ="select bSizeNumber from downFileTestData GROUP BY bSizeNumber";
	return sql;
}


private Map<String, Integer> getWeblostRateGradeListList(String startDate,
			String stopDate,String vlanGradeCompanyTextSelect,String vlanGradeCompanyText) {
	
	BSizeBean bean =  BSizeServie.getBSizeBean("丢包率");
	
	String sql1 = "select SUM(100) as sumCount,vlanId1 ,vlanId2 from webTestData where lostRate <="+bean.getMinSpeed();

	if(VLANGRADECOMPANYTEXTSELECTVALUE.equals(vlanGradeCompanyTextSelect)){
		
		sql1+=" and vlanId1 like '%"+vlanGradeCompanyText+"%'";
	}
	 sql1+="  and cmdTime BETWEEN '"+startDate+"' and '"+stopDate+"'  GROUP BY vlanId1 ,vlanId2";
	
	
	String sql2 = "select CAST(SUM( 100*(1 - ( lostRate-"+bean.getMinSpeed()+")/"+(bean.getMaxSpeed()-bean.getMinSpeed())+" ) )  as INT) as sumCount  ,vlanId1 ,vlanId2 from webTestData where " +
			"lostRate >"+bean.getMinSpeed()+" and lostRate < "+bean.getMaxSpeed();
	
	if(VLANGRADECOMPANYTEXTSELECTVALUE.equals(vlanGradeCompanyTextSelect)){
		
		sql2+=" and vlanId1 like '%"+vlanGradeCompanyText+"%'";
	}
	
	sql2+=" and cmdTime BETWEEN '"+startDate+"' and '"+stopDate+"'  GROUP BY vlanId1 ,vlanId2";
	
	
	RunSqlDao dao = new RunSqlDaoImpl();
	
	
	List list = dao.runSqlQuery(sql1);
	
	
	List list2 = dao.runSqlQuery(sql2);
	
	Map<String, Integer> countMap = new HashMap<String, Integer>();
	
	
	if(list!=null&&list.size()>0){
		
		for(Object obj:list){
			
			
			Map map =(Map) obj;
			String key = map.get("vlanId1").toString()+"_"+map.get("vlanId2").toString();
			if(countMap.get(key)!=null){
				
				countMap.put(key, (Integer.parseInt(map.get("sumCount").toString())+countMap.get(key)));
			}else{
				
				countMap.put(key, Integer.parseInt(map.get("sumCount").toString()));
			}
		}
	}
	if(list2!=null&&list2.size()>0){
		
		for(Object obj:list2){
			
			
			Map map =(Map) obj;
			String key = map.get("vlanId1").toString()+"_"+map.get("vlanId2").toString();
			if(countMap.get(key)!=null){
				
				countMap.put(key, (Integer.parseInt(map.get("sumCount").toString())+countMap.get(key)));
			}else{
				
				countMap.put(key, Integer.parseInt(map.get("sumCount").toString()));
			}
			
		}
	}
	
	return countMap ;
	
}



private Map<String, Integer> getWebShakeGradeListList(String startDate,
			String stopDate,String vlanGradeCompanyTextSelect,String vlanGradeCompanyText) {
	
	BSizeBean bean =  BSizeServie.getBSizeBean("网页时延抖动");
	
	
	String sql1 = "select SUM(100) as sumCount,vlanId1 ,vlanId2 from webTestData where shake <="+bean.getMinSpeed()+" and lostRate !=100 ";
	
	if(VLANGRADECOMPANYTEXTSELECTVALUE.equals(vlanGradeCompanyTextSelect)){
		
		 sql1+=" and vlanId1 like '%"+vlanGradeCompanyText+"%'";
	}
	
	 sql1+=" and cmdTime  BETWEEN '"+startDate+"' and '"+stopDate+"'  GROUP BY  vlanId1,vlanId2";

	
	String sql2 = "select CAST(SUM( 100*(1 - ( shake-"+bean.getMinSpeed()+")/"+(bean.getMaxSpeed()-bean.getMinSpeed())+" ) )  as INT) as sumCount  ,vlanId1,vlanId2 from webTestData where" +
			" shake >"+bean.getMinSpeed()+" and shake < "+bean.getMaxSpeed();

	if(VLANGRADECOMPANYTEXTSELECTVALUE.equals(vlanGradeCompanyTextSelect)){
		
		 sql2+=" and vlanId1 like '%"+vlanGradeCompanyText+"%'";
	}
	sql2+=	"  and cmdTime BETWEEN '"+startDate+"' and '"+stopDate+"'  GROUP BY vlanId1,vlanId2";
	
	RunSqlDao dao = new RunSqlDaoImpl();
	
	
	List list = dao.runSqlQuery(sql1);
	
	
	List list2 = dao.runSqlQuery(sql2);
	
	Map<String, Integer> countMap = new HashMap<String, Integer>();
	
	
	if(list!=null&&list.size()>0){
		
		for(Object obj:list){
			
			
			Map map =(Map) obj;
			String key = map.get("vlanId1").toString()+"_"+map.get("vlanId2").toString();
			countMap.put(key, Integer.parseInt(map.get("sumCount").toString()));
		}
	}
	if(list2!=null&&list2.size()>0){
		
		for(Object obj:list2){
			
			
			Map map =(Map) obj;
			String key = map.get("vlanId1").toString()+"_"+map.get("vlanId2").toString();
			if(countMap.get(key)!=null){
				
				countMap.put(key, (Integer.parseInt(map.get("sumCount").toString())+countMap.get(key)));
			}else{
				
				countMap.put(key, Integer.parseInt(map.get("sumCount").toString()));
			}
			
		}
	}
	
	return countMap ;
	
}


private Map<String, Integer> getWebtimedelayGradeList(String startDate,
			String stopDate ,String vlanGradeCompanyTextSelect,String vlanGradeCompanyText) {
	
	BSizeBean bean =  BSizeServie.getBSizeBean("网页时延");
	

	
	String sql1 = "select SUM(100) as sumCount  ,vlanId1 ,vlanId2 from webTestData where timedelayAvr <="+bean.getMinSpeed()+" and timedelayAvr>0 and cmdTime BETWEEN '"+startDate+"' and '"+stopDate+"' ";
	
	
	if(VLANGRADECOMPANYTEXTSELECTVALUE.equals(vlanGradeCompanyTextSelect)){
		
		sql1+=" and vlanId1 like '%"+vlanGradeCompanyText+"%'";
	}
	
	sql1+=" GROUP BY vlanId2,vlanId1";
	
	String sql2 = "select CAST(SUM( 100*(1 - ( timedelayAvr-"+bean.getMinSpeed()+")/"+(bean.getMaxSpeed()-bean.getMinSpeed())+" ) )  as INT) as sumCount,vlanId1  ,vlanId2 from webTestData where timedelayAvr >"
	+bean.getMinSpeed()+" and timedelayAvr < "+bean.getMaxSpeed()+" and cmdTime BETWEEN '"+startDate+"' and '"+stopDate+"' ";
	
	
	if(VLANGRADECOMPANYTEXTSELECTVALUE.equals(vlanGradeCompanyTextSelect)){
		
		sql2+=" and vlanId1 like '%"+vlanGradeCompanyText+"%'";
	}
	sql2+=" GROUP BY vlanId1,vlanId2";
	
	
	
	RunSqlDao dao = new RunSqlDaoImpl();
	
	
	List list = dao.runSqlQuery(sql1);
	
	
	List list2 = dao.runSqlQuery(sql2);
	
	Map<String, Integer> countMap = new HashMap<String, Integer>();
	
	
	if(list!=null&&list.size()>0){
		
		for(Object obj:list){
			
			
			Map map =(Map) obj;
			String key = map.get("vlanId1").toString()+"_"+map.get("vlanId2").toString();
			countMap.put(key, Integer.parseInt(map.get("sumCount").toString()));
		}
	}
	if(list2!=null&&list2.size()>0){
		
		for(Object obj:list2){
			
			
			Map map =(Map) obj;
			String key = map.get("vlanId1").toString()+"_"+map.get("vlanId2").toString();
			if(countMap.get(key)!=null){
				
				countMap.put(key, (Integer.parseInt(map.get("sumCount").toString())+countMap.get(key)));
			}else{
				
				countMap.put(key, Integer.parseInt(map.get("sumCount").toString()));
			}
			
		}
	}
	
	return countMap ;
	
}


public static void main(String[] args) {
	VlanGradeService sevcie =new VlanGradeServiceImpl();
	List<GradeBean> list = sevcie.getVlanGrade("2014-09-10 00:00:00","2014-09-12 00:00:00",VLANGRADECOMPANYTEXTSELECTVALUE,"1");
	 
	
	for(GradeBean bean:list){
		
		System.out.println(bean);
	}

}


	private Set<String> getVlanIdSet(Map<String, Integer> webTestCountList,
			Map<String, Integer> downFileTestCountList) {
		
		
		Set<String> set = new HashSet<String>();		
		


		for(String str:webTestCountList.keySet()){
			
			set.add(str);
			
		}
		
	for(String str:downFileTestCountList.keySet()){
			
			set.add(str);
			
		}

		

		
		
		
		return set;
	}

	private Map<String, Integer> getDownFileTestCountList(String startDate, String stopDate,String vlanGradeCompanyTextSelect,String vlanGradeCompanyText) {
		Map<String, Integer> countMap = new HashMap<String, Integer>();
		
		String sql ="select COUNT(*) as count,vlanId1,vlanId2 from downFileTestData where cmdTime BETWEEN '"+startDate+"' and '"+stopDate+"'";
		
		if(VLANGRADECOMPANYTEXTSELECTVALUE.equals(vlanGradeCompanyTextSelect)){
			
			sql+=" and vlanId1 like '%"+vlanGradeCompanyText+"%'";
		}
		sql+="  GROUP BY vlanId1,vlanId2";
		
		
		
		RunSqlDao dao = new RunSqlDaoImpl();
		 List list =dao.runSqlQuery(sql);
		 if(list!=null&&list.size()>0){
	
				for(Object obj:list){
					
					
					Map map =(Map) obj;
					String key = map.get("vlanId1").toString()+"_"+map.get("vlanId2").toString();
					countMap.put(key, Integer.parseInt(map.get("count").toString()));
				}
					
				}
			
				return countMap;
	}

	private Map<String, Integer>  getWebGradeCountList(String startDate, String stopDate) {
		
		
		String sql1 = "select SUM(100) as sumCount ,vlanId1,vlanId2 from webTestData where speed <=100 and cmdTime BETWEEN '"+startDate+"' and '"+stopDate+"'  GROUP BY vlanId1 ,vlanId2";

		
		String sql2 = "select CAST(SUM( 100*(1 - ( speed-100)/900 ) )  as INT) as sumCount ,vlanId1 ,vlanId2 from webTestData where speed >100 and speed < 1000 and cmdTime BETWEEN '"+startDate+"' and '"+stopDate+"'  GROUP BY vlanId1 ,vlanId2";
		
		
		RunSqlDao dao = new RunSqlDaoImpl();
		
		
		List list = dao.runSqlQuery(sql1);
		
		
		List list2 = dao.runSqlQuery(sql2);
		
		Map<String, Integer> countMap = new HashMap<String, Integer>();
		
		
		if(list!=null&&list.size()>0){
			
			for(Object obj:list){
				
				
				Map map =(Map) obj;
				
				countMap.put(map.get("vlanId1").toString()+"_"+map.get("vlanId2").toString(), Integer.parseInt(map.get("sumCount").toString()));
			}
		}
		if(list2!=null&&list2.size()>0){
			
			for(Object obj:list2){
				
				
				Map map =(Map) obj;
				
				if(countMap.get(map.get("vlanId2").toString())!=null){
					
					countMap.put(map.get("vlanId1").toString()+"_"+map.get("vlanId2").toString(), (Integer.parseInt(map.get("sumCount").toString())+countMap.get(map.get("vlanId2").toString())));
				}else{
					
					countMap.put(map.get("vlanId1").toString()+"_"+map.get("vlanId2").toString(), Integer.parseInt(map.get("sumCount").toString()));
				}
				
			}
		}
		
		return countMap ;
		
	}

	private Map<String, Integer> getWebTestCountList(String startDate, String stopDate,String vlanGradeCompanyTextSelect,String vlanGradeCompanyText) {
		
		Map<String, Integer> countMap = new HashMap<String, Integer>();
		
		String sql ="select COUNT(*) as count,vlanId1,vlanId2 from webTestData where  cmdTime BETWEEN '"+startDate+"' and '"+stopDate+"' ";
		
		
		if(VLANGRADECOMPANYTEXTSELECTVALUE.equals(vlanGradeCompanyTextSelect)){
			
			sql+=" and vlanId1 like '%"+vlanGradeCompanyText+"%'";	
		}
		
		
		sql+=	" GROUP BY vlanId1,vlanId2";
		
		
		
		RunSqlDao dao = new RunSqlDaoImpl();
		List list =  dao.runSqlQuery(sql);
		
	
		if(list!=null&&list.size()>0){
		
		for(Object obj:list){
			
			
			Map map =(Map) obj;
			String key = map.get("vlanId1").toString()+"_"+map.get("vlanId2").toString();
			countMap.put(key, Integer.parseInt(map.get("count").toString()));
		}
			
		}
	
		return countMap;
		
	
	}



}
